﻿
/*图标索引表*/
if exists (select * from dbo.sysobjects where id = object_id(N'ICONIndex') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
-- 删除表 
drop table ICONIndex
GO 
CREATE TABLE ICONIndex
(
[CODE] [int] IDENTITY(1,1) primary key NOT NULL,
	[keyword] [varchar](255) default '',
	[zhkeyword] [varchar](255) default '',
	[size12] float default 0,
	[icon12] float default 0,
	[size16] float default 0,
	[icon16] float default 0,
	[size24] float default 0,
	[icon24] float default 0,
	[size32] float default 0,
	[icon32] float default 0,
	[size48] float default 0,
	[icon48] float default 0,
	[size64] float default 0,
	[icon64] float default 0,
	[size128]float default 0,
	[icon128]float default 0,
	[size256]float default 0,
	[icon256]float default 0,
	[down] [int] default -1
);
GO
/*记录访问数量*/
CREATE TABLE [dbo].[ACount](
	[CODE] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[ADay] [date] NULL,
	[CountSum] [int] NULL
);
GO
/*存储过程*/
CREATE PROC [dbo].[UpdateACount]
@ADay date,
@countSum int
AS
update ACount set CountSum = @countSum
where ADay = @ADay AND CountSum < @countSum; 
GO
/*获取所有尺寸的ICON图标*/
CREATE PROC GetIconImagesAllSize
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon12 float,
	icon16 float,
	icon24 float,
	icon32 float,
	icon48 float,
	icon64 float,
	icon128 float,
	icon256 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon12,icon16,icon24,icon32,icon48,icon64,icon128,icon256
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/* end GetIconImagesAllSize*/
GO
/*获取12*12的ICON图片*/
CREATE PROC GetIconImagesSize12
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon12 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon12
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon12!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize12*/
GO
/*获取16*16的ICON图片*/
CREATE PROC GetIconImagesSize16
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon16 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon16
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon16!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize16*/
GO
/*获取24*24的ICON图片*/
CREATE PROC GetIconImagesSize24
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon24 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon24
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon24!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize24*/
Go
/*获取32*32的ICON图片*/
CREATE PROC GetIconImagesSize32
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon32 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon32
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon32!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize32*/
Go
/*获取48*48的ICON图片*/
CREATE PROC GetIconImagesSize48
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon48 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon48
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon48!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize48*/
Go
/*获取64*64的ICON图片*/
CREATE PROC GetIconImagesSize64
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon64 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon64
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon64!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize64*/
Go
/*获取128*128的ICON图片*/
CREATE PROC GetIconImagesSize128
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon128 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon128
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon128!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize128*/
Go
/*获取256*256的ICON图片*/
CREATE PROC GetIconImagesSize256
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	icon256 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,icon256
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where icon256!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetIconImagesSize256*/
Go

/*获取所有尺寸的PNG图标*/
CREATE PROC GetPngImagesAllSize
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size12 float,
	size16 float,
	size24 float,
	size32 float,
	size48 float,
	size64 float,
	size128 float,
	size256 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size12,size16,size24,size32,size48,size64,size128,size256
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/* end GetIconImagesAllSize*/
GO
/*获取12*12的PNG图片*/
CREATE PROC GetPngImagesSize12
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size12 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size12
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size12!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize12*/
GO
/*获取16*16的Png图片*/
CREATE PROC GetPngImagesSize16
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size16 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size16
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size16!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize16*/
GO
/*获取24*24的Png图片*/
CREATE PROC GetPngImagesSize24
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size24 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size24
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size24!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize24*/
Go
/*获取32*32的Png图片*/
CREATE PROC GetPngImagesSize32
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size32 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size32
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size32!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize32*/
Go
/*获取48*48的Png图片*/
CREATE PROC GetPngImagesSize48
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size48 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size48
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size48!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize48*/
Go
/*获取64*64的Png图片*/
CREATE PROC GetPngImagesSize64
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size64 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size64
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size64!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize64*/
Go
/*获取128*128的Png图片*/
CREATE PROC GetPngImagesSize128
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size128 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size128
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size128!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize128*/
Go
/*获取256*256的Png图片*/
CREATE PROC GetPngImagesSize256
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size256 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size256
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size256!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetPngImagesSize256*/
Go

/*获取所有尺寸的所有类型图标*/
CREATE PROC GetAllImagesAllSize
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size12 float,
	size16 float,
	size24 float,
	size32 float,
	size48 float,
	size64 float,
	size128 float,
	size256 float,
	icon12 float,
	icon16 float,
	icon24 float,
	icon32 float,
	icon48 float,
	icon64 float,
	icon128 float,
	icon256 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size12,size16,size24,size32,size48,size64,size128,size256,
icon12, icon16 , icon24 , icon32 , icon48 , icon64 , icon128 , icon256 
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/* end GetIconImagesAllSize*/
GO
/*获取12*12的所有类型的图片*/
CREATE PROC GetAllImagesSize12
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size12 float,
	icon12 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size12,icon12
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size12!=-1 or icon12!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize12*/
GO
/*获取16*16的所有类型的图片*/
CREATE PROC GetAllImagesSize16
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size16 float,
	icon16 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size16,icon16
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size16!=-1 or icon16!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize16*/
GO
/*获取24*24的所有类型图片*/
CREATE PROC GetAllImagesSize24
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size24 float,
	icon24 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size24,icon24
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size24!=-1 or icon24!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize24*/
Go
/*获取32*32的所有类型图片*/
CREATE PROC GetAllImagesSize32
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size32 float,
	icon32 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size32,icon32
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size32!=-1 or icon32!= -1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize32*/
Go
/*获取48*48的所有类型图片*/
CREATE PROC GetAllImagesSize48
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size48 float,
	icon48 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size48,icon48
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size48!=-1 or icon48!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize48*/
Go
/*获取64*64的所有类型的图片*/
CREATE PROC GetAllImagesSize64
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size64 float,
	icon64 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size64,icon64
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size64!=-1 or icon64!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize64*/
Go
/*获取128*128的所有类型的图片*/
CREATE PROC GetAllImagesSize128
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size128 float,
	icon128 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size128,icon128
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size128!=-1 or icon128!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize128*/
Go
/*获取256*256的所有类型的图片*/
CREATE PROC GetAllImagesSize256
@pageSize INT,
@pageNumber INT,
@keyword VARCHAR(255),
@totalRecordSum INT OUT
AS
declare @startIndex int
declare @endIndex int
set @startIndex = (@pageNumber -1) * @pageSize
set @endIndex = @pageNumber * @pageSize
DECLARE @resultTable TABLE
(
	rowID  INT,
	CODE INT,
	keyword VARCHAR(255),
	zhkeyword VARCHAR(255),
	size256 float,
	icon256 float
)
insert into @resultTable
select ROW_NUMBER() OVER(ORDER BY ICONIndex.CODE asc) as rowID,
CODE,keyword,zhkeyword,size256,icon256
from ICONIndex
INNER JOIN 
FREETEXTTABLE(ICONIndex,(keyword,zhkeyword), @keyword) AS tempTable
ON tempTable.[Key] = ICONIndex.CODE
where size256!=-1 or icon256!=-1
SELECT * from @resultTable 
where rowID between @startIndex+1 and @endIndex 
select @totalRecordSum = count(*) from @resultTable;
/*end GetAllImagesSize256*/
Go